import sqlite3 from 'sqlite3'
import { app } from 'electron'
import { join } from 'path'
import fs from 'fs'

// 获取项目根目录路径
const rootPath = app.getAppPath()

// 确保 db 目录存在
const dbDir = join(rootPath, 'db')
if (!fs.existsSync(dbDir)) {
  fs.mkdirSync(dbDir, { recursive: true })
}

// 设置数据库文件路径
const dbPath = join(dbDir, 'database.sqlite')
console.log('数据库路径:', dbPath)

// 启用详细日志
sqlite3.verbose()

const db = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('数据库连接失败:', err)
    return
  }
  console.log('数据库连接成功')
})

// 初始化数据库表
db.serialize(() => {
  // 创建 servers 表
  db.run(`
    CREATE TABLE IF NOT EXISTS servers (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      host TEXT NOT NULL,
      port INTEGER NOT NULL,
      username TEXT NOT NULL,
      password TEXT,
      description TEXT,
      create_time TEXT NOT NULL,
      update_time TEXT NOT NULL
    )
  `)

  // 创建 projects 表
  db.run(`
    CREATE TABLE IF NOT EXISTS projects (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      path TEXT NOT NULL,
      description TEXT,
      create_time TEXT NOT NULL,
      update_time TEXT NOT NULL
    )
  `)

  // 创建 project_envs 表
  db.run(`
    CREATE TABLE IF NOT EXISTS project_envs (
      id TEXT PRIMARY KEY,
      project_id TEXT NOT NULL,
      name TEXT NOT NULL,
      server_id TEXT NOT NULL,
      server_path TEXT NOT NULL,
      FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE,
      FOREIGN KEY (server_id) REFERENCES servers (id) ON DELETE CASCADE
    )
  `)

  // 创建 project_scripts 表
  db.run(`
    CREATE TABLE IF NOT EXISTS project_scripts (
      id TEXT PRIMARY KEY,
      project_id TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      content TEXT,
      command TEXT NOT NULL,
      scriptType TEXT NOT NULL,
      filename TEXT NOT NULL,
      FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
    )
  `)
  console.log('数据库初始化完成')
})
  
// 处理进程退出时关闭数据库连接
process.on('exit', () => {
  db.close((err) => {
    if (err) {
      console.error('关闭数据库失败:', err)
    }
  })
})

export default db
